package com.itmk.colloge.dao.impl;

import com.itmk.colloge.dao.CollogeDao;
import com.itmk.colloge.entity.Colloge;
import com.itmk.colloge.entity.CollogeParm;
import com.itmk.colloge.entity.TotalCologeVo;
import com.itmk.utils.JDBCUtils;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.List;

public class CollogeDaoImpl implements CollogeDao {
    //操作数据库
    public QueryRunner query = new QueryRunner(JDBCUtils.getDataSource(),true);
    //开启下划线->驼峰转换所用  colloge_id ->  collogeId
    BeanProcessor bean = new GenerousBeanProcessor();
    RowProcessor processor = new BasicRowProcessor(bean);

    @Override
    public void add(Colloge colloge) {
        //1、定义sql
        String sql = "insert into colloge(colloge_id,colloge_name,order_num) values(?,?,?)";
        //2、定义hadler
        BeanHandler handler = new BeanHandler(Colloge.class);
        //3、组装参数
        Object[] parms = {
                colloge.getCollogeId(),
                colloge.getCollogeName(),
                colloge.getOrderNum()
        };
        try {
            //4、入库处理
            query.insert(sql, handler, parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<Colloge> list(CollogeParm parm) {
        //1、定义sql
        //判断关键字是否存在
        if (StringUtils.isNotEmpty(parm.getCollogeName())) {
            StringBuilder sb = new StringBuilder("select * from (select colloge.* ,ROWNUM rm from colloge order by order_num desc) ");
            //排序
            sb.append(" where colloge_name like '%" + parm.getCollogeName() + "%' ");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler handler = new BeanListHandler(Colloge.class, processor);
            //3、设置参数
            try {
                return (List<Colloge>) query.query(sql, handler);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else {
            StringBuilder sb = new StringBuilder("select * from (select colloge.* ,ROWNUM rm from colloge order by order_num desc) ");
            sb.append("where rm >= ? and rm <= ?");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler handler = new BeanListHandler(Colloge.class, processor);
            //3、设置参数
            Object[] parms = {
                    (parm.getPage() - 1) * parm.getLimit(),
                    (parm.getPage()*parm.getLimit())
            };
            try {
                return (List<Colloge>) query.query(sql, handler, parms);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int getCount(CollogeParm parm) {
        //1、定义sql语句
        StringBuilder sb = new StringBuilder("select count(colloge_id) from colloge");
        //判断关键字是否存在
        if (StringUtils.isNotEmpty(parm.getCollogeName())) {
            sb.append(" where colloge_name like '%" + parm.getCollogeName() + "%'");

        }
            String sql = sb.toString();
            //2、定义handler
            ScalarHandler handler = new ScalarHandler();
            //3、执行查询
            try {
                return Integer.valueOf(this.query.query(sql, handler).toString());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        return 0;
    }

    @Override
    public Colloge getById(String collogeId) {
        //1、定义sql
        String sql = "select * from colloge where colloge_id =?";
        //2、定义handler
        BeanHandler<Colloge> handler = new BeanHandler<>(Colloge.class,processor);
        try {
          return  query.query(sql,handler,collogeId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void updateById(Colloge colloge) {
        //1、定义sql
        String sql = "update colloge set colloge_name=?,order_num=? where colloge_id =?";
        //2、定义参数
        Object[] parms = {
                colloge.getCollogeName(),
                colloge.getOrderNum(),
                colloge.getCollogeId()
        };
        try {
            query.update(sql,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void deleteById(String collogeId) {
        try {
            query.update("delete from colloge where colloge_id=?",collogeId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<Colloge> getAll() {

        try {
            return query.query("select * from colloge",new BeanListHandler<Colloge>(Colloge.class,processor));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int getTotal() {
        String sql = "select count(colloge_id) as total_colloge from colloge ";
         ScalarHandler handler = new ScalarHandler();
        try {
           return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
